%%%-------------------------------------------------------------------
%% @doc mysqlboy public API
%% @end
%%%-------------------------------------------------------------------

-module(mysqlboy).
-include_lib("eunit/include/eunit.hrl").
-export([checkout/1, checkin/2]).
-export([query/2, query/3, query/5]).
-export([insert/3, delete/3, select/3, update/4, replace/3]).

%%%-------------------------------------------------------------------
%%%-------------------------------------------------------------------
-export_type([query/0, query_param/0, query_filtermap_fun/0, query_timeout/0,
              query_result/0, server_reason/0]).

-type db() :: atom().
-type query_worker() :: pid().

-type column_name() :: binary().
-type row() :: [term()].
-type query() :: iodata().
-type query_param() :: no_params 
                     | [term()].

-type query_filtermap_fun() :: no_filtermap_fun 
                             | fun((row()) -> query_filtermap_res()) 
                             | fun(([column_name()], row()) -> query_filtermap_res()).
-type query_filtermap_res() :: boolean() 
                             | {true, term()}.

-type query_timeout() :: default_timeout 
                       | timeout().

-type server_reason() :: {Code :: integer(), SQLState :: binary() | undefined,
                          Message :: binary()}.
-type query_result() :: ok
                      | {ok, [column_name()], [row()]}
                      | {ok, [{[column_name()], [row()]}, ...]}
                      | {error, server_reason()}.

-type table() :: atom() | string() | binary().
-type datas() :: map().

%%%-------------------------------------------------------------------
%%%-------------------------------------------------------------------
-spec checkout( db() ) -> query_worker().
checkout(DB) ->
    poolboy:checkout(DB).

-spec checkin( db(), query_worker() ) -> ok.
checkin(DB, Worker) ->
    poolboy:checkin(DB, Worker).


-spec query( query_worker(), query() ) -> query_result().
query(Worker, Query) ->
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).

-spec query( query_worker(), query(), query_param() ) -> query_result().
query(Worker, Query, Params) ->
    query(Worker, Query, Params, no_filtermap_fun, default_timeout).

-spec query( query_worker(), query(), query_param(), query_filtermap_fun(), query_timeout() ) -> query_result().
query(Worker, Query, Params, FilterMap, Timeout) ->
    gen_server:call(Worker, {query, Query, Params, FilterMap, Timeout}).

    

%%%-------------------------------------------------------------------
%%%-------------------------------------------------------------------
-spec insert( query_worker(), table(), datas() ) -> query_result().
insert(Worker, Table, Datas) ->
    % insert into table (column1, column2) values (value1, value2);
    Query = lists:concat(["insert into ", Table, pack_insert(Datas), ";"]),
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).

-spec delete( query_worker(), table(), datas() ) -> query_result().
delete(Worker, Table, Datas) ->
    % delete from table where column1=value1 and column2=value2;
    Query = lists:concat(["delete from ", Table, pack_delete(Datas), ";"]),
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).

-spec select( query_worker(), table(), datas() ) -> query_result().
select(Worker, Table, Datas) ->
    % select * from table where column1=value1 and column2=value2;
    Query = lists:concat(["select * from ", Table, pack_selete(Datas), ";"]),
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).

-spec update( query_worker(), table(), datas(), datas() ) -> query_result().
update(Worker, Table, Datas, Conditions) ->
    % update table set column1=value1, column2=value2 where column3=value3 and column4=value4;
    Query = lists:concat(["update ", Table, pack_update(Datas), pack_selete(Conditions), ";"]),
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).

-spec replace( query_worker(), table(), datas() ) -> query_result().
replace(Worker, Table, Datas) ->
    % replace into table (column1, column2) values (value1, value2);
    Query = lists:concat(["replace into ", Table, pack_replace(Datas), ";"]),
    query(Worker, Query, no_params, no_filtermap_fun, default_timeout).


%%--------------------------------------------------------------------
pack_insert(Datas) when is_map(Datas)  -> pack_insert(maps:to_list(Datas));
pack_insert(Datas) when is_list(Datas) ->
    Column = lists:concat(["(", string:join([to_list(C)                || {C, _} <- Datas], ","), ")"]),
    Values = lists:concat(["(", string:join([to_list(convert_value(P)) || {_, P} <- Datas], ","), ")"]),
    lists:concat([Column, " values ", Values]).

%%
pack_delete(Datas) -> pack_selete(Datas).

%% 
pack_selete(Datas) when is_map(Datas)  -> pack_selete( maps:to_list(Datas), "" );
pack_selete(Datas) when is_list(Datas) -> pack_selete( Datas,               "" ).

pack_selete([{Column, Value} | Datas], "")         -> pack_selete(Datas, lists:concat([            " where ", to_list(Column), "=", convert_value(Value)]));
pack_selete([{Column, Value} | Datas], Statements) -> pack_selete(Datas, lists:concat([Statements, " and ",   to_list(Column), "=", convert_value(Value)]));
pack_selete([],                        Statements) -> Statements.

%%
pack_update(Datas) when is_map(Datas)  -> pack_update( maps:to_list(Datas), "" );
pack_update(Datas) when is_list(Datas) -> pack_update( Datas,               "" ).

pack_update([{Column, Value} | Datas], "")         -> pack_update(Datas, lists:concat([            " set ", to_list(Column), "=", convert_value(Value)]));
pack_update([{Column, Value} | Datas], Statements) -> pack_update(Datas, lists:concat([Statements, ", ",    to_list(Column), "=", convert_value(Value)]));
pack_update([],                        Statements) -> Statements.

%%
pack_replace(Datas) -> pack_insert(Datas).


%---------------------------------------------------------------------
convert_value(D) when is_integer(D) -> D;
convert_value(D)                    -> io_lib:format("~p", [to_list(D)]).

to_list(D) when is_integer(D)   -> integer_to_list(D);
to_list(D) when is_binary(D)    -> binary_to_list(D);
to_list(D) when is_atom(D)      -> atom_to_list(D);
to_list(D) -> D.



